package com.zretc.emptraining.daoimpl;

import java.util.List;

import com.zretc.emptraining.dao.EmpScoreDao;
import com.zretc.emptraining.dbtools.DBTools;
import com.zretc.emptraining.dbtools.PageData;
import com.zretc.emptraining.entity.CourseEva;
import com.zretc.emptraining.entity.EmpScore;

public class EmpScoreDaoImpl implements EmpScoreDao {

	@Override
	public boolean addEmpScore(EmpScore empScore) {
		String sql = "insert into empscore values (null,?,?,?,?,?,?)";
		int n = DBTools.getUpdate(sql,empScore.getEmpNo(), empScore.getCpNo(), empScore.getTeacherNo(),
				empScore.getScore(), empScore.getNotes(), empScore.getPass());
		return n > 0;
	}
	
	@Override
	public List<EmpScore> getEmpScoreAll() {
		String sql = "SELECT empscore.scoreNo,empscore.empNo,(select empName from emp where empscore.empNo=emp.empNo)as empName,empscore.cpNo,\r\n" + 
				"(SELECT course.courseName from  course WHERE course.courseNo=\r\n" + 
				"(select courseplan.courseNo from courseplan where courseplan.cpNo=empscore.cpNo))as courseName,\r\n" + 
				"empscore.teacherNo,(select teach.teacherName from teach where teach.teacherNo=empscore.teacherNo)as teacherName,\r\n" + 
				"empscore.score,empscore.notes,empscore.pass FROM empscore";
		List<EmpScore> list = (List<EmpScore>) DBTools.exQuery(sql, EmpScore.class, null);
		return list;
	}

	@Override
	public boolean updEmpScore(EmpScore empScore) {
		String sql = "update empscore set empNo=?,cpNo=?,teacherNo=?,score=?,notes=?,pass=? where scoreNo=?";
		int n = DBTools.getUpdate(sql, empScore.getEmpNo(), empScore.getCpNo(), empScore.getTeacherNo(),
				empScore.getScore(), empScore.getNotes(), empScore.getPass(),empScore.getScoreNo());
		return n > 0;
	}

	
	@Override
	public PageData<EmpScore> getEmpScoreByEmpNo(int page, int pageSize, int empNo) {
		String sql = "SELECT es.scoreNo,es.empNo,e.empName,es.cpNo,c.courseName,es.teacherNo,t.teacherName,es.score,es.notes,es.pass\r\n" + 
				"FROM (empscore es LEFT JOIN emp e ON es.empNo = e.empNo)\r\n" + 
				"LEFT JOIN courseplan cp ON es.cpNo = cp.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = es.teacherNo \r\n" + 
				"WHERE es.empNo = ?";
		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, empNo);
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreByCourseName(int page, int pageSize, String courseName) {
		String sql = "SELECT es.scoreNo,es.empNo,e.empName,es.cpNo,c.courseName,es.teacherNo,t.teacherName,es.score,es.notes,es.pass\r\n" + 
				"FROM (empscore es LEFT JOIN emp e ON es.empNo = e.empNo)\r\n" + 
				"LEFT JOIN courseplan cp ON es.cpNo = cp.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = es.teacherNo \r\n" + 
				"WHERE courseName LIKE ?";
		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, "%"+courseName+"%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreByEmpName(int page, int pageSize, String empName) {
		String sql = "SELECT es.scoreNo,es.empNo,e.empName,es.cpNo,c.courseName,es.teacherNo,t.teacherName,es.score,es.notes,es.pass\r\n" + 
				"FROM (empscore es LEFT JOIN emp e ON es.empNo = e.empNo)\r\n" + 
				"LEFT JOIN courseplan cp ON es.cpNo = cp.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = es.teacherNo \r\n" + 
				"WHERE empName LIKE ?";
		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, "%"+empName+"%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreByTeacherName(int page, int pageSize, String teacherName) {
		String sql = "SELECT es.scoreNo,es.empNo,e.empName,es.cpNo,c.courseName,es.teacherNo,t.teacherName,es.score,es.notes,es.pass\r\n" + 
				"FROM (empscore es LEFT JOIN emp e ON es.empNo = e.empNo)\r\n" + 
				"LEFT JOIN courseplan cp ON es.cpNo = cp.cpNo\r\n" + 
				"LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n" + 
				"LEFT JOIN teach t ON t.teacherNo = es.teacherNo \r\n" + 
				"WHERE teacherName LIKE ?";
		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, "%"+teacherName+"%");
	}
	
	@Override
	public List<EmpScore> getEmpScoreByCourseNo(int cpNo) {
		String sql = "select * from empscore where cpNo=?";
		return (List<EmpScore>) DBTools.exQuery(sql, EmpScore.class, cpNo);
	}
	
	@Override
	public List<EmpScore> getEmpScoreByDeptNo(int deptNo) {
		String sql = "select * from empscore where deptNo=?";
		return (List<EmpScore>) DBTools.exQuery(sql, EmpScore.class, deptNo);
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreByEmpNo(int page, int pageSize, int empNo, String pass) {
		String sql = "SELECT empscore.scoreNo,empscore.empNo,(select empName from emp where empscore.empNo=emp.empNo)as empName,empscore.cpNo,\r\n"
				+ "(SELECT course.courseName from  course WHERE course.courseNo=\r\n"
				+ "(select courseplan.courseNo from courseplan where courseplan.cpNo=empscore.cpNo))as courseName,(SELECT course.coursePic from  course WHERE course.courseNo=\r\n" + 
				"(select courseplan.courseNo from courseplan where courseplan.cpNo=empscore.cpNo))as coursePic,\r\n"
				+ "empscore.teacherNo,(select teach.teacherName from teach where teach.teacherNo=empscore.teacherNo)as teacherName,\r\n"
				+ "empscore.score,empscore.notes,empscore.pass FROM empscore LEFT JOIN emp ON empscore.empNo=emp.empNo WHERE empscore.empNo = ? and empscore.pass like ?";

		return (PageData<EmpScore>) DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, empNo, "%" + pass + "%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreByPage(int page, int pageSize, String empNo) {
		String sql = "select * from empscore where empNo like ?";
		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, "%" + empNo + "%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreByTeacherNo(int page, int pageSize, int teacherNo) {
		String sql = "select * from empscore where teacherNo=?";
		return (PageData<EmpScore>) DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, teacherNo);
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreDeptByCpNo(int page, int pageSize, int deptNo, String cpNo, String pass) {
		String sql = "SELECT es.scoreNo,es.empNo,e.empName,es.cpNo,c.courseName,es.teacherNo,t.teacherName,es.score,es.notes,es.pass\r\n"
				+ "FROM (empscore es LEFT JOIN emp e ON es.empNo = e.empNo)\r\n"
				+ "LEFT JOIN courseplan cp ON es.cpNo = cp.cpNo\r\n"
				+ "LEFT JOIN course c ON c.courseNo = cp.courseNo\r\n"
				+ "LEFT JOIN teach t ON t.teacherNo = es.teacherNo \r\n"
				+ "where e.deptNo=? and cp.cpNo like ? and es.pass like ?";

		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, deptNo, "%" + cpNo + "%", "%" + pass + "%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreDeptByEmpName(int page, int pageSize, int deptNo, String empName,
			String pass) {
		String sql = "SELECT empscore.scoreNo,empscore.empNo,(select empName from emp where empscore.empNo=emp.empNo)as empName,empscore.cpNo,\r\n"
				+ "(SELECT course.courseName from  course WHERE course.courseNo=\r\n"
				+ "(select courseplan.courseNo from courseplan where courseplan.cpNo=empscore.cpNo))as courseName,\r\n"
				+ "empscore.teacherNo,(select teach.teacherName from teach where teach.teacherNo=empscore.teacherNo)as teacherName,\r\n"
				+ "empscore.score,empscore.notes,empscore.pass FROM empscore LEFT JOIN emp on empscore.empNo=emp.empNo where emp.deptNo=? and emp.empName like ? and empscore.pass like ?";

		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, deptNo, "%" + empName + "%",
				"%" + pass + "%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreDeptByEmpNo(int page, int pageSize, int deptNo, String empNo, String pass) {
		String sql = "SELECT empscore.scoreNo,empscore.empNo,(select empName from emp where empscore.empNo=emp.empNo)as empName,empscore.cpNo,\r\n"
				+ "(SELECT course.courseName from  course WHERE course.courseNo=\r\n"
				+ "(select courseplan.courseNo from courseplan where courseplan.cpNo=empscore.cpNo))as courseName,\r\n"
				+ "empscore.teacherNo,(select teach.teacherName from teach where teach.teacherNo=empscore.teacherNo)as teacherName,\r\n"
				+ "empscore.score,empscore.notes,empscore.pass FROM empscore LEFT JOIN emp on empscore.empNo=emp.empNo where emp.deptNo=? and empscore.empNo like ? and empscore.pass like ?";

		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, deptNo, "%" + empNo + "%", "%" + pass + "%");
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreTeaByEmpName(int page, int pageSize, int teacherNo, String empName,String pass,int cpNo) {
		String sql = " select empscore.scoreNo,empscore.empNo,empscore.cpNo,empscore.score,empscore.notes,empscore.pass,emp.empName from empscore left join emp on empscore.empNo=emp.empNo where teacherNo=? and emp.empName like ? and empscore.pass like ? and empscore.cpNo = ?";

		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, teacherNo, "%" + empName + "%",
				"%" + pass + "%",cpNo);
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreTeaByEmpNo(int page, int pageSize, int teacherNo, String empNo, String pass,int cpNo) {
		String sql = " select empscore.scoreNo,empscore.empNo,empscore.cpNo,empscore.score,empscore.notes,empscore.pass,emp.empName from empscore left join emp on empscore.empNo=emp.empNo where teacherNo=? and empscore.empNo =  ? and empscore.pass like ? and empscore.cpNo = ?";

		return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, teacherNo, empNo, "%" + pass + "%",cpNo);
	}
	
	@Override
	public PageData<EmpScore> getEmpScoreTeaByEmpState(int page, int pageSize, int teacherNo, int pass) {
		
				String sql = " select empscore.scoreNo,empscore.empNo,empscore.cpNo,empscore.score,empscore.notes,empscore.pass,emp.empName from empscore left join emp on empscore.empNo=emp.empNo where teacherNo=? and empscore.pass = ?";

				return DBTools.exQueryByPage(sql, EmpScore.class, page, pageSize, teacherNo, pass);
	}
	
}
